from typing import Optional

from sqlalchemy import Column, Integer, DateTime, ForeignKey, Text, text
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.sql import func
from pydantic import BaseModel, Field

# 创建基类
class Base(DeclarativeBase):
  pass

class User(Base):
  __tablename__ = 'user'

  id = Column(Integer, primary_key=True, autoincrement=True)
  username = Column(String, unique=True, nullable=False)
  password = Column(String, nullable=False)
  email = Column(String, unique=True, nullable=False)
  reg_time = Column(DateTime, default=func.datetime('now', 'localtime'))

# 用于测试
class Category(Base):
  __tablename__ = "category"
  # __pydantic_model__ = CategorySchema  # 指定模型对应的Schema类.省略可自动生成,但是建议指定.

  id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
  name: Mapped[str] = mapped_column(String(100), unique=True, index=True, nullable=False)
  description: Mapped[str] = mapped_column(String(255), default="")

# 定义表模型
class TaskQueue(Base):
  __tablename__ = 'task_queue'

  id = Column(Integer, primary_key=True, autoincrement=True)
  mid = Column(Integer, nullable=True) # master的id , 比如 对应 PaperAnalyzeMain.id
  recvTime = Column(DateTime, server_default=func.datetime('now', 'localtime')) #
  sts = Column(Integer, default=0)
  stype = Column(String, default='download_by_doi')
  updtime = Column(DateTime, default=func.datetime('now', 'localtime'))
  cont = Column(String)

class PaperAnalyzeMain(Base):
  __tablename__ = 'paper_analyze_main'
  id = Column(Integer, primary_key=True, autoincrement=True)
  paper_file = Column(String, nullable=False)  # 论文的文件(相对路径)
  file_md5 = Column(String, nullable=False)  # 论文的文件的md5值
  user_id = Column(Integer, nullable=False)  # 上传用户的id
  upd_time = Column(DateTime, default=func.datetime('now', 'localtime') )  # 上传时间

class PaperReference(Base):
  __tablename__ = 'paper_reference'

  id = Column(Integer, primary_key=True, autoincrement=True)
  paper_id = Column(Integer, ForeignKey('paper_analyze_main.id'), nullable=False)  # 对应 paper_analyze_main 的 id
  doc_file = Column(String)  # 文献的文档
  title = Column(String)  # 文献的标题
  authors = Column(String)  # 文献的作者
  publication_date = Column(String)  # 文献的出版日期
  journal = Column(String)  # 文献的期刊
  issue = Column(String)  # 文献的期号
  volume = Column(String)  # 文献的卷
  year = Column(String)  # 文献的出版年份
  pages = Column(String)  # 文献的页码
  abstract = Column(Text)  # 文献的摘要
  keywords = Column(String)  # 文献的关键词
  doi = Column(String)  # 文献的唯一标识符
  resource = Column(String)  # 文献的来源
  ori_ref = Column(Text)  # 原始的参考文献的信息
  upd_time = Column(DateTime, default=func.datetime('now', 'localtime') )  # 处理时间
  dl_receipt_id = Column(Integer)  # 下载回执的id
  dl_save_path = Column(String)  # 下载好的文件的保存路径

def init_tables(engine):
  Base.metadata.create_all(engine)

r"""
CREATE TABLE task_queue (
    id       INTEGER   NOT NULL
                       PRIMARY KEY AUTOINCREMENT,
    mid      INTEGER   NULL DEFAULT -1,
    recvTime TIMESTAMP DEFAULT (datetime('now', 'localtime') ),
    sts      INTEGER   DEFAULT 0,
    stype    TEXT      NOT NULL DEFAULT 'download_by_doi',
    updtime  TIMESTAMP DEFAULT (datetime('now', 'localtime') ),
    cont     TEXT
);

"""